/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package DAO;

import DTO.AccountMailDTO;
import UTIL.SqlDataAccessHelper;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.sql.Statement;

/**
 *
 * @author NxSiHMr0D
 */
public class AccountMailDAO {

    Connection _newConnect;

    /**
     * get all of accounts.
     * @return
     * @throws java.sql.SQLException
     */
    public ArrayList<AccountMailDTO> getListAccount() throws SQLException {
        ArrayList<AccountMailDTO> lstAcc = new ArrayList<AccountMailDTO>();
        if (_newConnect == null || _newConnect.isClosed()) {
            _newConnect = SqlDataAccessHelper.getConnection();
        }
        Statement uprs = (Statement) _newConnect.createStatement();
        ResultSet st = uprs.executeQuery("SELECT * FROM Accounts");
        while (st.next()) {
            AccountMailDTO account = new AccountMailDTO();
            int id = st.getInt("ID");
            account.setId(id);
            id = st.getInt("portNumber");
            account.setPortNumber(id);
            String s = st.getString("AccountName");
            account.setAccountName(s);
            s = st.getString("MailAddress");
            account.setMailAddress(s);
            s = st.getString("IncomingUserName");
            account.setIncomingUserName(s);
            s = st.getString("IncomingServerName");
            account.setIncommingServerName(s);
            s = st.getString("IncomingServerType");
            account.setIncomingServerType(s);
            s = st.getString("OutGoingUserName");
            account.setOutgoingUserName(s);
            s = st.getString("OutGoingServerName");
            account.setOutgoingServerName(s);
            s = st.getString("Password");
            account.setPassword(s);
            lstAcc.add(account);
        }
        return lstAcc;

    }

    /**
     * get account Mail by ID
     * @param id
     * @return
     * @throws java.sql.SQLException
     */
    public AccountMailDTO getAccountMail(int id) throws SQLException {
        // AccountMailDTO Acc = new AccountMailDTO();
        if (_newConnect == null || _newConnect.isClosed()) {
            _newConnect = SqlDataAccessHelper.getConnection();
        }
        PreparedStatement uprs = _newConnect.prepareStatement("SELECT * FROM Accounts WHERE ID=?");
        uprs.setInt(1, id);
        ResultSet st = uprs.executeQuery();
        while (st.next()) {
            AccountMailDTO account = new AccountMailDTO();
            int ids = st.getInt("ID");
            account.setId(ids);
            ids = st.getInt("PortNumber");
            account.setPortNumber(ids);
            String s = st.getString("AccountName");
            account.setAccountName(s);
            s = st.getString("MailAddress");
            account.setMailAddress(s);
            s = st.getString("IncomingUserName");
            account.setIncomingUserName(s);
            s = st.getString("IncomingServerName");
            account.setIncommingServerName(s);
            s = st.getString("IncomingServerType");
            account.setIncomingServerType(s);
            s = st.getString("OutGoingUserName");
            account.setOutgoingUserName(s);
            s = st.getString("OutGoingServerName");
            account.setOutgoingServerName(s);
            s = st.getString("Password");
            account.setPassword(s);

            //lstAcc.add(account);
            return account;
        }
        return null;



    }

    /**
     * get account mail by name.
     * @param name
     * @return
     * @throws java.sql.SQLException
     */
    public AccountMailDTO getAccountMail(String name) throws SQLException {
        // AccountMailDTO Acc = new AccountMailDTO();
        if (_newConnect == null || _newConnect.isClosed()) {
            _newConnect = SqlDataAccessHelper.getConnection();
        }
        PreparedStatement uprs = _newConnect.prepareStatement("SELECT * FROM Accounts WHERE AccountName =?");
        uprs.setString(1, name);
        ResultSet st = uprs.executeQuery();
        while (st.next()) {
            AccountMailDTO account = new AccountMailDTO();
            int ids = st.getInt("ID");
            account.setId(ids);
            ids = st.getInt("PortNumber");
            account.setPortNumber(ids);
            String s = st.getString("AccountName");
            account.setAccountName(s);
            s = st.getString("MailAddress");
            account.setMailAddress(s);
            s = st.getString("IncomingUserName");
            account.setIncomingUserName(s);
            s = st.getString("IncomingServerName");
            account.setIncommingServerName(s);
            s = st.getString("IncomingServerType");
            account.setIncomingServerType(s);
            s = st.getString("OutGoingUserName");
            account.setOutgoingUserName(s);
            s = st.getString("OutGoingServerName");
            account.setOutgoingServerName(s);
            s = st.getString("Password");
            account.setPassword(s);
            //lstAcc.add(account);
            return account;
        }
        return null;



    }

    /**
     * get account mail by address.
     * @param sender
     * @return
     * @throws java.sql.SQLException
     */
    public AccountMailDTO getMailFromAddress(String sender) throws SQLException {
        //throw new UnsupportedOperationException("Not yet implemented");
        if (_newConnect == null || _newConnect.isClosed()) {
            _newConnect = SqlDataAccessHelper.getConnection();
        }
        PreparedStatement uprs = _newConnect.prepareStatement("SELECT * FROM Accounts WHERE MailAddress =?");
        uprs.setString(1, sender);
        ResultSet st = uprs.executeQuery();
        while (st.next()) {
            AccountMailDTO account = new AccountMailDTO();
            int ids = st.getInt("ID");
            account.setId(ids);
            ids = st.getInt("PortNumber");
            account.setPortNumber(ids);
            String s = st.getString("AccountName");
            account.setAccountName(s);
            s = st.getString("MailAddress");
            account.setMailAddress(s);
            s = st.getString("IncomingUserName");
            account.setIncomingUserName(s);
            s = st.getString("IncomingServerName");
            account.setIncommingServerName(s);
            s = st.getString("IncomingServerType");
            account.setIncomingServerType(s);
            s = st.getString("OutGoingUserName");
            account.setOutgoingUserName(s);
            s = st.getString("OutGoingServerName");
            account.setOutgoingServerName(s);
            s = st.getString("Password");
            account.setPassword(s);
            //lstAcc.add(account);
            return account;
        }
        return null;

    }

    /**
     * update information account mail.
     * @param accountUpdate
     * @throws java.sql.SQLException
     */
    public void updateAccountMail(AccountMailDTO accountUpdate) throws SQLException {
        if (_newConnect == null || _newConnect.isClosed()) {
            _newConnect = SqlDataAccessHelper.getConnection();
        }
        accountUpdate.setIncomingUserName(accountUpdate.getAccountName());
        PreparedStatement uprs =
                _newConnect.prepareStatement("UPDATE Accounts SET AccountName = ?, MailAddress = ?," +
                "IncomingUserName=?,IncomingServerName =?,IncomingServerType=?,OutGoingUserName=?,OutGoingServerName=?,Password=?,PortNumber=?" +
                " WHERE id = ?");
        uprs.setString(1, accountUpdate.getAccountName());
        uprs.setString(2, accountUpdate.getMailAddress());
        uprs.setString(3, accountUpdate.getIncomingUserName());
        uprs.setString(4, accountUpdate.getIncommingServerName());
        uprs.setString(5, accountUpdate.getIncomingServerType());
        uprs.setString(6, accountUpdate.getOutgoingUserName());
        uprs.setString(7, accountUpdate.getOutgoingServerName());
        uprs.setString(8, accountUpdate.getPassword());
        uprs.setInt(9, accountUpdate.getPortNumber());
        uprs.setInt(10, accountUpdate.getId());

        int st = uprs.executeUpdate();

    }

    /**
     * insert new account mail.
     * @param accountInsert
     * @return
     * @throws java.sql.SQLException
     */
    public int insertAccountMail(AccountMailDTO accountInsert) throws SQLException {
        if (_newConnect == null || _newConnect.isClosed()) {
            _newConnect = SqlDataAccessHelper.getConnection();
        }
//        PreparedStatement uprs =
//                _newConnect.prepareStatement("INSERT INTO Accounts(AccountName,MailAddress,IncomingUserName,IncomingServerName,IncomingServerType,OutGoingUserName,OutGoingServerName,Password)" +
//                " VALUES(?,?,?,?,?,?,?,?)");
//        uprs.setString(1, accountInsert.getAccountName());
//        uprs.setString(2, accountInsert.getMailAddress());
//        uprs.setString(3, accountInsert.getIncomingUserName());
//        uprs.setString(4, accountInsert.getIncommingServerName());
//        uprs.setString(5, accountInsert.getIncomingServerType());
//        uprs.setString(6, accountInsert.getOutgoingUserName());
//        uprs.setString(7, accountInsert.getOutgoingServerName());
//        uprs.setString(8, accountInsert.getPassword());
//        //uprs.setInt(8, accountInsert.getId());
//
//        uprs.execute();

        int IDSender = 1;
        // String sqlQuerry = "INSERT INTO Accounts(AccountName,MailAddress,IncomingUserName,IncomingServerName,IncomingServerType,OutGoingUserName,OutGoingServerName,Password) VALUES(?,?,?,?,?,?,?,?)";
        //PreparedStatement st = _newConnect.prepareStatement(sqlQuerry);
        accountInsert.setIncomingUserName(accountInsert.getAccountName());
        Statement uprs = _newConnect.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);
        ResultSet st = uprs.executeQuery("SELECT * FROM Accounts");

        st.moveToInsertRow();
        st.updateString("AccountName", accountInsert.getAccountName());
        st.updateString("MailAddress", accountInsert.getMailAddress());
        st.updateString("IncomingUserName", accountInsert.getIncomingUserName());
        st.updateString("IncomingServerName", accountInsert.getIncommingServerName());
        st.updateString("IncomingServerType", accountInsert.getIncomingServerType());
        st.updateString("OutGoingUserName", accountInsert.getOutgoingUserName());
        st.updateString("OutGoingServerName", accountInsert.getOutgoingServerName());
        st.updateString("Password", accountInsert.getPassword());
        st.updateInt("PortNumber", accountInsert.getPortNumber());
        //ResultSet res = st.executeQuery();
        st.insertRow();
        st.beforeFirst();
        if (st.next()) {
            //_newConnect.close();
            IDSender = st.getInt("ID");
        //return IDSender;
        }
        String sqlQuery = "select max(ID)as IDs from Accounts";
        Statement stmt = _newConnect.createStatement();
        ResultSet newCode = stmt.executeQuery(sqlQuery);
        newCode.next();

        IDSender = newCode.getInt(1);
        //return -1;
        return IDSender;

    }

    /**
     * delete account mail from database.
     * @param accountDelete
     * @throws java.sql.SQLException
     */
    public void deleteAccountMail(AccountMailDTO accountDelete) throws SQLException {
        if (_newConnect == null || _newConnect.isClosed()) {
            _newConnect = SqlDataAccessHelper.getConnection();
        }
        PreparedStatement uprs =
                _newConnect.prepareStatement("DELETE FROM Accounts WHERE ID = ?");
        uprs.setInt(1, accountDelete.getId());
        uprs.executeUpdate();
    }
}
